package com.xgpro.pc.archives.service.impl;

import com.xgpro.common.enums.ResultCodeEnum;
import com.xgpro.common.exceptions.BusinessRuntimeException;
import com.xgpro.common.results.ResponseResult;
import com.xgpro.common.utils.ExcelUtils;
import com.xgpro.common.utils.ReadPropertiesUtil;
import com.xgpro.common.utils.WriteExcelUtil;
import com.xgpro.domain.archives.entity.*;
import com.xgpro.domain.talentintroduction.entity.User;
import com.xgpro.mapper.archives.*;
import com.xgpro.mapper.talentintroduction.UserMapper;
import com.xgpro.pc.archives.service.ExcelImportService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;
import tk.mybatis.mapper.entity.Example;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
@Transactional(readOnly = false)
public class ExcelImportServiceImpl implements ExcelImportService {

    @Resource
    private UserMapper userMapper;

    @Resource
    private ExcelImportMapper importMapper;

    @Resource
    private RankMapper rankMapper;

    @Resource
    private ArchivesMapper archivesMapper;

    @Override
    public ResponseResult rankImpotr(MultipartFile file) {
        try {
            List<List<Object>> rowlist = new ArrayList<>();
            try {
                rowlist = ExcelUtils.getBankListByExcel(file.getInputStream(), file.getOriginalFilename());
            } catch (IOException e) {
                e.printStackTrace();
            }
            List<User> list = new ArrayList<>();
            List<List<Object>> errorlist = new ArrayList<>();
            for (int i = 0; i < rowlist.size(); i++) {
                List<Object> row = rowlist.get(i);
                if(CollectionUtils.isEmpty(row)){
                    continue;
                }
                Boolean b = correctList(row);
                if(b){
                    continue;
                }
                if(row.size()!=5){
                    row.add("信息不全!");
                    errorlist.add(row);
                    continue;
                }
                String username = (String) row.get(0);
                String userId = (String) row.get(1);
                User user = userMapper.getUser(username, userId);
                if (ObjectUtils.isEmpty(user)){
                    row.add("用户名或userId有误!");
                    errorlist.add(row);
                    continue;
                }
                String queue = (String) row.get(2);
                String name = (String) row.get(3);
                Rank rank = getRank(name, queue);
                if(ObjectUtils.isEmpty(rank)){
                    row.add("职级或序列有误!");
                    errorlist.add(row);
                    continue;
                }
                user.setRankId(rank.getId());
                String time = (String) row.get(4);
                if(ObjectUtils.isEmpty(time) || "空值".equals(time)){
                    row.add("职级晋级时间有误!");
                    errorlist.add(row);
                    continue;
                }
                Date date = getRankDate(time);
                user.setRankUpdate(date);
                list.add(user);
            }
            int errorNumber = errorlist.size();
            int successNumber = batchUpdateData(list);
            if(successNumber==0){
                return ResponseResult.ok("导入失败,请确认模板文件");
            }else if(errorNumber>0){
                return ResponseResult.ok("部分导入成功,请确认模板文件");
            }
            return ResponseResult.ok("成功导入" + successNumber + "条");
        }catch (BusinessRuntimeException e){
            e.printStackTrace();
            return ResponseResult.failure(e.getErrorCode(),e.getMessage());
        }

    }

    private int batchUpdateData(List<User> list) {
        int result = 0;
        if (CollectionUtils.isEmpty(list)) {
            return result;
        }
        //每次最多插入1000条数据
        int beatchMaxSize = 1000;
        while (list.size() > 0) {
            if (list.size() < beatchMaxSize) {
                beatchMaxSize = list.size();
            }
            try {
                List<User> batchList = list.subList(0, beatchMaxSize);
                /*批量写入数据库的方法*/
                result  = archivesMapper.update(batchList);
            } catch (Exception e) {
                e.printStackTrace();
            }
            list.removeAll(list.subList(0, beatchMaxSize));
        }
        return result;
    }

    @Override
    public ResponseResult perImpotr(MultipartFile file) {
        try {
            List<List<Object>> rowlist = read(file);
            List<PerForMance> list = new ArrayList<>();
            List<List<Object>> errorlist = new ArrayList<>();
            for (int i = 0; i < rowlist.size(); i++) {
                List<Object> row = rowlist.get(i);

                if(CollectionUtils.isEmpty(row)){
                    continue;
                }
                Boolean b = correctList(row);
                if(b){
                    continue;
                }
                if(row.size()!=6){
                    row.add("信息不全!");
                    errorlist.add(row);
                    continue;
                }

                String username = (String) row.get(0);
                String userId = (String) row.get(1);
                User user = userMapper.getUser(username, userId);
                if (ObjectUtils.isEmpty(user)){
                    row.add("用户名或userId有误!");
                    errorlist.add(row);
                    continue;
                }
                String perTime = (String) row.get(2);
                if(ObjectUtils.isEmpty(perTime) || "空值".equals(perTime)){
                    row.add("绩效标题有误!");
                    errorlist.add(row);
                    continue;
                }
                String introduce = (String) row.get(3);
                if(ObjectUtils.isEmpty(introduce) || "空值".equals(introduce)){
                    row.add("绩效得分有误!");
                    errorlist.add(row);
                    continue;
                }
                String perdept = (String) row.get(4);
                if(ObjectUtils.isEmpty(perdept) || "空值".equals(perdept)){
                    row.add("绩效审核部门有误!");
                    errorlist.add(row);
                    continue;
                }
                String time = (String) row.get(5);
                if(ObjectUtils.isEmpty(time) || "空值".equals(time)){
                    row.add("绩效创建时间有误!");
                    errorlist.add(row);
                    continue;
                }
                PerForMance per=new PerForMance();
                per.setUserId(user.getId());
                per.setPerTime(perTime);
                per.setIntroduce(introduce);
                per.setPerdept(perdept);
                per.setCreateTime(time);
                Integer id = user.getId();
                Integer integer = importMapper.deletePer(id, time);
                if(!list.contains(per)){
                    list.add(per);
                }
            }
            int errorNumber = errorlist.size();
            int successNumber = batchPerData(list);
            if(successNumber==0){
                return ResponseResult.ok("导入失败,请确认模板文件");
            }else if(errorNumber>0){
                return ResponseResult.ok("部分导入成功,请确认模板文件");
            }
            return ResponseResult.ok("成功导入" + successNumber + "条");
        }catch (BusinessRuntimeException e){
            return ResponseResult.failure(e.getErrorCode(),e.getMessage());
        }
    }

    private int batchPerData(List<PerForMance> perlist) {
        int result = 0;
        if (CollectionUtils.isEmpty(perlist)) {
            return result;
        }
        //每次最多插入1000条数据
        int beatchMaxSize = 1000;
        while (perlist.size() > 0) {
            if (perlist.size() < beatchMaxSize) {
                beatchMaxSize = perlist.size();
            }
            try {
                List<PerForMance> list = perlist.subList(0, beatchMaxSize);
                /*批量写入数据库的方法*/
                result  = importMapper.insertPer(list);
            } catch (Exception e) {
                e.printStackTrace();
            }
            perlist.removeAll(perlist.subList(0, beatchMaxSize));
        }
        return result;
    }

    @Override
    public ResponseResult titleImpotr(MultipartFile file) {
        try {
            List<List<Object>> rowlist = read(file);
            List<JobTitle> list = new ArrayList<>();
            List<List<Object>> errorlist = new ArrayList<>();
            for (int i = 0; i < rowlist.size(); i++) {
                List<Object> row = rowlist.get(i);
                if(CollectionUtils.isEmpty(row)){
                    continue;
                }
                Boolean b = correctList(row);
                if(b){
                    continue;
                }
                if(row.size()!=7){
                    row.add("信息不全!");
                    errorlist.add(row);
                    continue;
                }
                String username = (String) row.get(0);
                String userId = (String) row.get(1);
                User user = userMapper.getUser(username, userId);
                if (ObjectUtils.isEmpty(user)){
                    row.add("用户名或userId有误!");
                    errorlist.add(row);
                    continue;
                }
                String jobTitle = (String) row.get(2);
                if(ObjectUtils.isEmpty(jobTitle) || "空值".equals(jobTitle)){
                    row.add("职称有误!");
                    errorlist.add(row);
                    continue;
                }
                user.setJobTitle(jobTitle);
                userMapper.updateByPrimaryKeySelective(user);
                String majorType = (String) row.get(3);
                if(ObjectUtils.isEmpty(majorType) || "空值".equals(majorType)){
                    row.add("职称专业类别有误!");
                    errorlist.add(row);
                    continue;
                }
                String certificateTime = (String) row.get(4);
                if(ObjectUtils.isEmpty(certificateTime) || "空值".equals(certificateTime)){
                    row.add("职称取证时间有误!");
                    errorlist.add(row);
                    continue;
                }
                String validTime = (String) row.get(5);
                if(ObjectUtils.isEmpty(validTime) || "空值".equals(validTime)){
                    row.add("职称有效时间有误!");
                    errorlist.add(row);
                    continue;
                }
                String license = (String) row.get(6);
                if(ObjectUtils.isEmpty(license) || "空值".equals(license)){
                    row.add("职称发证单位有误!");
                    errorlist.add(row);
                    continue;
                }
                JobTitle title=new JobTitle();
                title.setUserId(user.getId());
                title.setMajorType(majorType);
                title.setCertificateTime(certificateTime);
                title.setValidTime(validTime);
                title.setLicense(license);
                Integer id = user.getId();
                Integer integer = importMapper.deleteTitle(id);
                if(!list.contains(title)){
                    list.add(title);
                }
            }
            int errorNumber = errorlist.size();
            int successNumber = batchTitleData(list);
            if(successNumber==0){
                return ResponseResult.ok("导入失败,请确认模板文件");
            }else if(errorNumber>0){
                return ResponseResult.ok("部分导入成功,请确认模板文件");
            }
            return ResponseResult.ok("成功导入" + successNumber + "条");
        }catch (BusinessRuntimeException e){
            return ResponseResult.failure(e.getErrorCode(),e.getMessage());
        }
    }


    private int batchTitleData(List<JobTitle> perlist) {
        int result = 0;
        if (CollectionUtils.isEmpty(perlist)) {
            return result;
        }
        //每次最多插入1000条数据
        int beatchMaxSize = 1000;
        while (perlist.size() > 0) {
            if (perlist.size() < beatchMaxSize) {
                beatchMaxSize = perlist.size();
            }
            try {
                List<JobTitle> list = perlist.subList(0, beatchMaxSize);
                /*批量写入数据库的方法*/
                result  = importMapper.insertTitle(list);
            } catch (Exception e) {
                e.printStackTrace();
            }
            perlist.removeAll(perlist.subList(0, beatchMaxSize));
        }
        return result;
    }




    @Override
    public ResponseResult projectImpotr(MultipartFile file) {
        try {
            List<List<Object>> rowlist = read(file);
            List<Project> list = new ArrayList<>();
            List<List<Object>> errorlist = new ArrayList<>();
            for (int i = 0; i < rowlist.size(); i++) {
                List<Object> row = rowlist.get(i);
                if(CollectionUtils.isEmpty(row)){
                    continue;
                }
                Boolean b = correctList(row);
                if(b){
                    continue;
                }
                if(row.size()!=11){
                    row.add("信息不全!");
                    errorlist.add(row);
                    continue;
                }
                String username = (String) row.get(1);
                String userId = (String) row.get(2);
                User user = userMapper.getUser(username, userId);
                if (ObjectUtils.isEmpty(user)){
                    row.add("用户名或userId有误!");
                    errorlist.add(row);
                    continue;
                }
                String projectPost = (String) row.get(3);
                if(ObjectUtils.isEmpty(projectPost) || "空值".equals(projectPost)){
                    row.add("项目职务有误!");
                    errorlist.add(row);
                    continue;
                }
                String projectNumber = (String) row.get(4);
                if(ObjectUtils.isEmpty(projectNumber) || "空值".equals(projectPost) ){
                    row.add("项目编号有误!");
                    errorlist.add(row);
                    continue;
                }
                String projectName = (String) row.get(5);
                if(ObjectUtils.isEmpty(projectName) || "空值".equals(projectPost)){
                    row.add("项目名称有误!");
                    errorlist.add(row);
                    continue;
                }
                String level = (String) row.get(6);
                if(ObjectUtils.isEmpty(level) || "空值".equals(projectPost)){
                    row.add("项目类别有误!");
                    errorlist.add(row);
                    continue;
                }
                Integer projectLevel = getType(level);
                if(projectLevel==0){
                    row.add("项目类别有误!");
                    errorlist.add(row);
                    continue;
                }
                String projectIntroduce = (String) row.get(7);
                if(ObjectUtils.isEmpty(projectIntroduce) || "空值".equals(projectPost)){
                    row.add("项目简介有误!");
                    errorlist.add(row);
                    continue;
                }
                String projectAchievement = (String) row.get(8);
                if(ObjectUtils.isEmpty(projectAchievement) || "空值".equals(projectPost)){
                    row.add("项目成果有误!");
                    errorlist.add(row);
                    continue;
                }
                String time = (String) row.get(9);
                if(ObjectUtils.isEmpty(time) || "空值".equals(time)){
                    row.add("项目开始时间有误!");
                    errorlist.add(row);
                    continue;
                }
                Date projectStarttime = getDate(time);
                String projectEndtime = (String) row.get(10);
                if(ObjectUtils.isEmpty(projectEndtime) || "空值".equals(projectEndtime) ){
                    row.add("项目结束时间有误!");
                    errorlist.add(row);
                    continue;
                }
                Date date = getDate(projectEndtime);

                Project project=new Project();
                project.setProjectPost(projectPost);
                project.setProjectNumber(projectNumber);
                project.setProjectName(projectName);
                project.setProjectLevel(projectLevel);
                project.setProjectIntroduce(projectIntroduce);
                project.setProjectAchievement(projectAchievement);
                project.setProjectStarttime(projectStarttime);
                project.setProjectEndtime(date);
                project.setUserId(user.getId());
                if(!list.contains(project)){
                    list.add(project);
                }
            }
            int errorNumber = errorlist.size();
            int successNumber = batchProjectData(list);
            if(successNumber==0){
                return ResponseResult.ok("导入失败,请确认模板文件");
            }else if(errorNumber>0){
                return ResponseResult.ok("部分导入成功,请确认模板文件");
            }
            return ResponseResult.ok("成功导入" + successNumber + "条");
        }catch (BusinessRuntimeException e){
            return ResponseResult.failure(e.getErrorCode(),e.getMessage());
        }
    }

    private int batchProjectData(List<Project> perlist) {
        int result = 0;
        if (CollectionUtils.isEmpty(perlist)) {
            return result;
        }
        //每次最多插入1000条数据
        int beatchMaxSize = 1000;
        while (perlist.size() > 0) {
            if (perlist.size() < beatchMaxSize) {
                beatchMaxSize = perlist.size();
            }
            try {
                List<Project> list = perlist.subList(0, beatchMaxSize);
                /*批量写入数据库的方法*/
                result  = importMapper.insertProject(list);
            } catch (Exception e) {
                e.printStackTrace();
            }
            perlist.removeAll(perlist.subList(0, beatchMaxSize));
        }
        return result;
    }

    @Override
    public ResponseResult patentImpotr(MultipartFile file) {
        try {
            List<List<Object>> rowlist = read(file);
            List<Patent> list = new ArrayList<>();
            List<List<Object>> errorlist = new ArrayList<>();
            for (int i = 0; i < rowlist.size(); i++) {
                List<Object> row = rowlist.get(i);
                if(CollectionUtils.isEmpty(row)){
                    continue;
                }
                Boolean b = correctList(row);
                if(b){
                    continue;
                }
                if(row.size()!=9){
                    row.add("信息不全!");
                    errorlist.add(row);
                    continue;
                }
                String username = (String) row.get(1);
                String userId = (String) row.get(2);
                User user = userMapper.getUser(username, userId);
                if (ObjectUtils.isEmpty(user)){
                    row.add("用户名或userId有误!");
                    errorlist.add(row);
                    continue;
                }
                String patentName = (String) row.get(3);
                if(ObjectUtils.isEmpty(patentName) || "空值".equals(patentName)){
                    row.add("专利名称有误!");
                    errorlist.add(row);
                    continue;
                }
                String type = (String) row.get(4);
                if(ObjectUtils.isEmpty(type) || "空值".equals(type)){
                    row.add("专利类型有误!");
                    errorlist.add(row);
                    continue;
                }
                Integer patentType =getType(type);
                if(patentType==0){
                    row.add("专利类型有误!");
                    errorlist.add(row);
                    continue;
                }
                String time = (String) row.get(5);
                if(ObjectUtils.isEmpty(time) || "空值".equals(time)){
                    row.add("专利获取时间有误!");
                    errorlist.add(row);
                    continue;
                }
                Date licenseTime = getDate(time);

                String patentNumber = (String) row.get(6);
                if(ObjectUtils.isEmpty(patentNumber) || "空值".equals(patentNumber)){
                    row.add("专利申请号有误!");
                    errorlist.add(row);
                    continue;
                }
                String patentPost = (String) row.get(7);
                if(ObjectUtils.isEmpty(patentPost) || "空值".equals(patentPost)){
                    row.add("专利参与角色有误!");
                    errorlist.add(row);
                    continue;
                }
                String patentPerson = (String) row.get(8);
                if(ObjectUtils.isEmpty(patentPerson) || "空值".equals(patentPerson)){
                    row.add("专利权人有误!");
                    errorlist.add(row);
                    continue;
                }
                Patent patent=new Patent();
                patent.setName(patentName);
                patent.setPatentType(patentType);
                patent.setLicenseTime(licenseTime);
                patent.setPatentNumber(patentNumber);
                patent.setPatentPost(patentPost);
                patent.setPatentPerson(patentPerson);
                patent.setUserId(user.getId());
                if(!list.contains(patent)){
                    list.add(patent);
                }
            }
            int errorNumber = errorlist.size();
            int successNumber = batchPatentData(list);
            if(successNumber==0){
                return ResponseResult.ok("导入失败,请确认模板文件");
            }else if(errorNumber>0){
                return ResponseResult.ok("部分导入成功,请确认模板文件");
            }
            return ResponseResult.ok("成功导入" + successNumber + "条");
        }catch (BusinessRuntimeException e){
            return ResponseResult.failure(e.getErrorCode(),e.getMessage());
        }
    }

    private int batchPatentData(List<Patent> perlist) {
        int result = 0;
        if (CollectionUtils.isEmpty(perlist)) {
            return result;
        }
        //每次最多插入1000条数据
        int beatchMaxSize = 1000;
        while (perlist.size() > 0) {
            if (perlist.size() < beatchMaxSize) {
                beatchMaxSize = perlist.size();
            }
            try {
                List<Patent> list = perlist.subList(0, beatchMaxSize);
                /*批量写入数据库的方法*/
                result  = importMapper.insertPatent(list);
            } catch (Exception e) {
                e.printStackTrace();
            }
            perlist.removeAll(perlist.subList(0, beatchMaxSize));
        }
        return result;
    }
    @Override
    public ResponseResult sixImpotr(MultipartFile file) {
        try {
            List<List<Object>> rowlist = read(file);
            List<SixSigma> list = new ArrayList<>();
            List<List<Object>> errorlist = new ArrayList<>();
            for (int i = 0; i < rowlist.size(); i++) {
                List<Object> row = rowlist.get(i);
                if(CollectionUtils.isEmpty(row)){
                    continue;
                }
                Boolean b = correctList(row);
                if(b){
                    continue;
                }
                if(row.size()!=6){
                    row.add("信息不全!");
                    errorlist.add(row);
                    continue;
                }
                String username = (String) row.get(1);
                String userId = (String) row.get(2);
                User user = userMapper.getUser(username, userId);
                if (ObjectUtils.isEmpty(user)){
                    row.add("用户名或userId有误!");
                    errorlist.add(row);
                    continue;
                }
                String sixSigmaGrade = (String) row.get(3);
                if(ObjectUtils.isEmpty(sixSigmaGrade) || "空值".equals(sixSigmaGrade)){
                    row.add("六西格玛等级名称有误!");
                    errorlist.add(row);
                    continue;
                }
                String acquisitionTime = (String) row.get(4);
                if(ObjectUtils.isEmpty(acquisitionTime) || "空值".equals(acquisitionTime)){
                    row.add("六西格玛取得时间有误!");
                    errorlist.add(row);
                    continue;
                }
                String certificateNumber = (String) row.get(5);
                if(ObjectUtils.isEmpty(certificateNumber) || "空值".equals(certificateNumber)){
                    row.add("六西格玛证书编号有误!");
                    errorlist.add(row);
                    continue;
                }
                SixSigma sigma=new SixSigma();
                sigma.setSixSigmaGrade(sixSigmaGrade);
                sigma.setAcquisitionTime(acquisitionTime);
                sigma.setCertificateNumber(certificateNumber);
                sigma.setUserId(user.getId());
                Integer id = user.getId();
                Integer integer = importMapper.deleteSixSigma(id);
                if(!list.contains(sigma)){
                    list.add(sigma);
                }
            }
            int errorNumber = errorlist.size();
            int successNumber = batchSixData(list);
            if(successNumber==0){
                return ResponseResult.ok("导入失败,请确认模板文件");
            }else if(errorNumber>0){
                return ResponseResult.ok("部分导入成功,请确认模板文件");
            }
            return ResponseResult.ok("成功导入" + successNumber + "条");
        }catch (BusinessRuntimeException e){
            return ResponseResult.failure(e.getErrorCode(),e.getMessage());
        }
    }


    public Integer getType(String name) {
        String a = ReadPropertiesUtil.readUrl(name);
        if(ObjectUtils.isEmpty(a)){
            return 0;
        }else{
            Integer of = Integer.valueOf(a);
            return of;
        }
    }


    private int batchSixData(List<SixSigma> perlist) {
        int result = 0;
        if (CollectionUtils.isEmpty(perlist)) {
            return result;
        }
        //每次最多插入1000条数据
        int beatchMaxSize = 1000;
        while (perlist.size() > 0) {
            if (perlist.size() < beatchMaxSize) {
                beatchMaxSize = perlist.size();
            }
            try {
                List<SixSigma> list = perlist.subList(0, beatchMaxSize);
                /*批量写入数据库的方法*/
                result  = importMapper.insertSixSigma(list);
            } catch (Exception e) {
                e.printStackTrace();
            }
            perlist.removeAll(perlist.subList(0, beatchMaxSize));
        }
        return result;
    }

    public  Boolean correctList(List<Object> list){
        int a=0;
        for (int i = 0; i < list.size(); i++) {
            if(list.get(i).equals("空值")){
                a++;
            }
        }
        if(a==list.size()){
            return true;
        }
        return false;
    }

    private List<List<Object>> read(MultipartFile file){
        List<List<Object>> rowlist = new ArrayList<>();
        try {
            rowlist = ExcelUtils.getBankListByExcel(file.getInputStream(), file.getOriginalFilename());
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rowlist;
    }

    public Date getDate(String time) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = null;
        try {
            date = sdf.parse(time);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  date ;
    }

    public Date getRankDate(String time) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        Date date = null;
        try {
            date = sdf.parse(time);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  date ;
    }

    private Rank getRank(String name,String queue){
        if(!ObjectUtils.isEmpty(name) && !ObjectUtils.isEmpty(queue)) {
            Example rankExample = new Example(Rank.class);
            Example.Criteria rankCriteria = rankExample.createCriteria();
            rankCriteria.andEqualTo("name", name);
            rankCriteria.andEqualTo("queue", queue);
            Rank  rank = rankMapper.selectOneByExample(rankExample);
            return rank;
        }else{
            throw new BusinessRuntimeException(ResultCodeEnum.INCOMPLETE_PROMOTION_INFORMATION.getCode(),ResultCodeEnum.INCOMPLETE_PROMOTION_INFORMATION.getMessage());
        }
    }

}